package util.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import util.Config;

public class MySqlConn 
{
    private Connection _conn = null;
    private Statement _stmt = null;
    private ResultSet _rs = null;

    private String dbhost = Config.my_db_host;
    private String dbName = Config.my_db_name;
    private String username = Config.my_db_user;
    private String password = Config.my_db_pass;
    private String dburl = "jdbc:mysql://" + dbhost + "/" + dbName;
  
    public void openConn()
    {
        try
        {
            if (_conn == null)
            {
            	Class.forName("com.mysql.jdbc.Driver"); 
                _conn = DriverManager.getConnection(dburl, username, password);
                _stmt = _conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            }
        }
        catch (Exception ex)
        {
            System.out.print(ex.getMessage() + " MYSQL Failed to open connection!");
        }
    }

    public void closeConn()
    {
        if (_conn != null)
        {
            try
            {
            	if(_rs != null) _rs.close();
            	if(_stmt != null) _stmt.close();
                if(_conn != null) _conn.close();
            }
            catch (Exception ex)
            {
                System.out.println(ex.getMessage() + " MYSQL Failed to close connection!");
            }
            finally
            {
            	_rs = null;
            	_stmt = null;
                _conn = null;
            }
        }
    }

    public int executeUpdate(String sql)
    {
        if (_conn == null)
        {
            openConn();
        }
        int commandFlag = 0;
        try
        {   
        	commandFlag = _stmt.executeUpdate(sql);
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage() + " MYSQL executeUpdaterError!");
        }
        return commandFlag;
    }

    public int executeGetid(String sql)
    {
        if (_conn == null)
        {
            openConn();
        }
        int create_id = 0;
        try
        {   
        	_stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        	_rs = _stmt.getGeneratedKeys (); 
        	if(_rs.next())
        	{
        		create_id = _rs.getInt(1); 
        	} 	
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage() + " MYSQL executeUpdaterError!");
        }
        return create_id;
    }
    
    public ResultSet executeQuery(String sql)
    {
        try
        {
            if (_conn == null)
            {
                openConn();
            }
            try
            {
            	_rs = _stmt.executeQuery(sql);
            }
            catch (Exception dbe)
            {
                System.out.println(dbe.getMessage());
            }
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage() + " MYSQL executeQueryError!");
        }
        return _rs;
    }
}
